/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package grest.model;
import java.sql.*;
import java.util.ArrayList;
import org.firebirdsql.jdbc.*;
//import sun.org.mozilla.javascript.ObjArray;


/**
 *
 * @author Agustin, Santiago, Rodrigo
 * 
 * 
 *
 * 
 * 
 */
public class DataIO {
    private static Driver d = null;
    public  static Connection c = null;
    private static String databaseURL =""; //"jdbc:firebirdsql:localhost:/media/Datos/Santiago/Universidad/Bases de Datos/TPFinalV2/gRest/db/gRest.gdb";
    private static String user = "";
    private static String password = "";
    private static final String driverName = "org.firebirdsql.jdbc.FBDriver";
    private static Statement s = null;
    private static ResultSet rs = null;
    private static ResultSetMetaData rsMetaData = null;
    
    public static void setPass(String pass)
    {
        DataIO.password=pass;
    }
    
    public static void setUser(String User)
    {
        DataIO.user=User;
    }
    
    
    public static void setUrl(String Url)
    {
        DataIO.databaseURL="jdbc:firebirdsql:"+Url;
    }
    
    public static boolean init() {
    boolean r = true;
    try {
          Class.forName (driverName);
        } catch (ClassNotFoundException nfe) {
          // A call to Class.forName() forces us to consider this exception :-)...
          System.err.println("Firebird JCA-JDBC driver not found in class path");
          System.err.println(nfe.getMessage());
          r=false;
        }
    try {
            // We pass the entire database URL, but we could just pass "jdbc:interbase:"
            d = java.sql.DriverManager.getDriver (databaseURL);
            System.out.println("Firebird JCA-JDBC driver version " +
                            d.getMajorVersion() +
                            "." +
                            d.getMinorVersion() +
                            " registered with driver manager.");
        } catch (java.sql.SQLException e) {
            System.out.print("Unable to find Firebird JCA-JDBC driver among the registered drivers.");
           // doException(e,"Unable to find Firebird JCA-JDBC driver among the registered drivers.");
            r=false;
        }
        try {
          c = java.sql.DriverManager.getConnection(databaseURL, user, password);
          System.out.println ("Connection established.");
          c.setAutoCommit(false);
          System.out.println("Auto-commit is disabled.");
          java.sql.DatabaseMetaData dbMetaData = c.getMetaData();
          // Ok, let's query a driver/database capability
          if (dbMetaData.supportsTransactions())
            System.out.println ("Transactions are supported.");
          else
            System.out.println ("Transactions are not supported.");
        } catch(java.sql.SQLException e) {
            System.out.print("Unable to establish a connection through the driver manager.");
           // doException(e,"Unable to establish a connection through the driver manager.");
            r=false;
        }
        return r;
    }
 
    
     public static boolean doCommit() {
        boolean r=true;
        try {
            c.commit();
        } catch (SQLException e) {
           System.out.print("Unable to Commit");
            r=false;
        }        
        return r;
    }
     
    public static ResultSet getResultSet(final String query) {
        try {
            rs = null;
            rsMetaData = null;
            s = c.createStatement();
            rs = s.executeQuery(query); // select   
            rsMetaData = rs.getMetaData();
        } catch (SQLException e) {
            System.out.print("Unable to get ResultSet from ["+query+"]");
            //doException(e,"Unable to get ResultSet from ["+query+"]");
        }
        return rs;
    }
    
    public static boolean executeQuery(final String query) {
        boolean r=true;
        try {
            s = c.createStatement();
            s.executeUpdate (query); // update, insert, delete, etc.
        } catch (SQLException e) {
            System.out.println("Unable to execute ["+query+"]");
            //doException(e,"Unable to execute ["+query+"]");
            r=false;
        }        
        return r;
    }
    public static void doException(SQLException e,final String msg) {
        e.printStackTrace();
        System.err.println (msg);
        //showSQLException(e);
        rs = null;
        rsMetaData = null;
    }
  public static ResultSet executeSProcedure(StoredProcedure procedure) throws SQLException{
       Integer cont=1;
       //Armamos la llamada al sp
       String call = "{call "+procedure.getName()+"(";
       for (Object param : procedure.getParamsIn()){
            call+=",?";
       }
       call=call.replaceFirst("\\,", "");
       for (Object param : procedure.getParamsOut()){
            call+=",?";   
       }
       call+=")}";
        CallableStatement cs = c.prepareCall(call);
        FirebirdCallableStatement fbCs = (FirebirdCallableStatement) cs;
        fbCs.setSelectableProcedure(true);
        for (Object param : procedure.getParamsIn()){
            cs.setObject(cont, param);
            cont++;
        }
        for (Integer param : procedure.getParamsOut()){
            cs.registerOutParameter(cont,param);
            cont++;
        }
        return cs.executeQuery();             
    }
  
    public static ArrayList<Object> executeEProcedure(StoredProcedure procedure) throws SQLException {
       Integer cont=1;
       ArrayList<Object> r = new ArrayList<Object>();
       //Armamos la llamada al sp
       String call = "{call "+procedure.getName()+"(";
       for (Object param : procedure.getParamsIn()){
            call+=",?";
       }
       call=call.replaceFirst("\\,", "");
       for (Object param : procedure.getParamsOut()){
            call+=",?";   
       }
       call+=")}";
        CallableStatement cs = c.prepareCall(call);
        for (Object param : procedure.getParamsIn()){
            /*if(param instanceof Integer) cs.setInt(cont,(Integer) param);
            if(param instanceof String) cs.setString(cont,(String) param);
            if(param instanceof Date) cs.setDate(cont,(Date) param);
            if(param instanceof Time) cs.setTime(cont,(Time) param);
            if(param instanceof Double) cs.setDouble(cont,(Double) param);
            if(param instanceof Long) cs.setLong(cont,(Long) param);
            if(param instanceof Short) cs.setShort(cont,(Short) param);
            if(param==null) cs.setNull(cont, Types.NULL); */
            cs.setObject(cont, param);
            cont++;
        }
        for (Integer param : procedure.getParamsOut()){
            cs.registerOutParameter(cont,param);
            cont++;
        }
        cs.execute();
        cont=1;
        for (Integer param : procedure.getParamsOut()){
               r.add(cs.getObject(1));
               cont++;
        }
        return r;
    }
}